package live.common;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import live.common.Page;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;


public class DBHelper {

	private Class beanClass = null;
	private QueryRunner run = null;
	private DataSource ds = null;
	private Connection conn = null;
	static DBHelper instance=null;
	
	

	/**
	 * 设置数据连接
	 * 
	 * @throws Exception
	 */
	
	public static synchronized DBHelper getInstance() {
		instance = new DBHelper("jdbc/business");
		return instance;
	}
	
	public DBHelper(String env){
		try {
			ds = (DataSource) new InitialContext().lookup("java:comp/env/"+env);
			run = new QueryRunner(ds);
			conn = ds.getConnection();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
//			throw new NamingException("NamingException in [jdbc/business]\n"
//					+ e.getMessage());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	/**
	 * 根据无参sql语句进行查询，并返回一个对象，用于单条记录的查询
	 * 
	 * @param sql
	 * @return Object
	 * @throws Exception
	 */
	public Object query(String sql) throws SQLException {
		if (ds == null || beanClass == null)
			return null;

		ResultSetHandler h = new BeanHandler(beanClass);
		Object result = null;
		try {
			result = run.query(sql, h);
		} catch (SQLException e) {
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	/**
	 * 根据有参sql语句进行查询，并返回一个对象，用于单条记录的查询
	 * 
	 * @param sql
	 * @param args
	 * @return Object
	 * @throws Exception
	 */
	public Object query(String sql, Object... args) throws SQLException {
		if (ds == null || beanClass == null)
			return null;

		ResultSetHandler h = new BeanHandler(beanClass);
		Object result = null;

		try {
			result = run.query(sql, args, h);
		} catch (SQLException e) {
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	/**
	 * 根据无参sql语句进行查询，并返回一个数据集，用于多条记录的查询
	 * 
	 * @param sql
	 * @return ArrayList
	 * @throws Exception
	 */
	public ArrayList queryRecords(String sql) throws SQLException {
		if (ds == null || beanClass == null)
			return null;

		ResultSetHandler h = new BeanListHandler(beanClass);
		ArrayList result = null;

		try {
			result = (ArrayList) run.query(sql, h);
			// obtain the quaried records
		} catch (SQLException e) {
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}

		return result;
	}

	/**
	 * 根据有参sql语句进行查询，并返回一个数据集，用于多条记录的查询
	 * 
	 * @param sql
	 * @param args
	 * @return
	 * @throws Exception
	 */
	public ArrayList queryRecords(String sql, Object... args)
			throws SQLException {
		if (ds == null || beanClass == null)
			return null;

		ResultSetHandler h = new BeanListHandler(beanClass);
		ArrayList result = null;

		try {
			result = (ArrayList) run.query(sql, args, h);

			// obtain the quaried records
		} catch (SQLException e) {
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}

		return result;
	}

	/**
	 * 根据无参sql语句进行(map)查询，并返回一个数据集，用于多条记录的查询
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public List queryRecordsMap(String sql) throws SQLException {
		List result = null;
		try {
			result = (List) run.query(sql, new MapListHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	public Object queryRecordMap(String sql) throws SQLException {
		Object result = null;
		try {
			result = (Object) run.query(sql, new MapHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	/**
	 * 根据有参sql语句进行(map)查询，并返回一个数据集，用于多条记录的查询
	 * 
	 * @param sql
	 * @param args
	 * @return
	 * @throws Exception
	 */
	public List queryRecordsMap(String sql, Object... args) throws SQLException {
		List result = null;
		try {
			result = (List) run.query(sql, args, new MapListHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	public Object queryRecordMap(String sql, Object... args)
			throws SQLException {
		Object result = null;
		try {
			result = (Object) run.query(sql, args, new MapHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	/**
	 * 根据无参sql语句进行数据更新，并返回更新后影响的记录数
	 * 
	 * @param sql
	 * @return int
	 * @throws SQLException
	 */
	public int update(String sql) throws SQLException {
		if (ds == null)
			return 0;

		int result = 0;

		try {
			result = run.update(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}

		return result;
	}

	/**
	 * 根据有参sql语句进行数据更新，并返回更新后影响的记录数
	 * 
	 * @param sql
	 * @param args
	 * @return int
	 * @throws SQLException
	 */
	public int update(String sql, Object... args) throws SQLException {
		if (ds == null)
			return -1;

		int result = 0;

		try {
			result = run.update(sql, args);
		} catch (SQLException e) {
			throw new SQLException("SQLException in [" + sql + "]\n"
					+ e.getMessage());
		}
		return result;
	}

	/**
	 * 使用存储过程查询数据，支持分页 parm 输入参数的值集合，null则说明无参数 outputParm
	 * 输出参数的类型集合，null则说明无输出参数 outputList 输出参数的返回值集合，和outputParm同时使用
	 * 
	 * @throws SQLException
	 */
	public List queryByProc(String procSql, Object[] parm, String[] outputParm,
			List outputList) throws SQLException {
		final String proc = procSql;
		final Object[] param = parm;
		final String[] outputParam = outputParm;
		final List ret = new ArrayList();
		final List outputlst = outputList;
		Map map = null;
		CallableStatement stmt = conn.prepareCall(proc);
		if (param != null) {// 给各个参数赋值，包括输出参数
			for (int i = 0; i < param.length; i++) {
				stmt.setObject(i + 1, param[i]);
			}
		}

		if (outputParam != null) {// 注册输出参数的类型
			for (int i = 0; i < outputParam.length; i++) {
				stmt.registerOutParameter(parm.length + i,
						getTypeByOutPutParam(outputParam[i]));
			}
		}
		ResultSet rs = stmt.executeQuery();

		ResultSetMetaData rsmt = rs.getMetaData();
		int count = rsmt.getColumnCount();// 字段个数
		while (rs.next()) {
			map = new HashMap();
			for (int i = 0; i < count; i++) {
				map.put(rsmt.getColumnName(i + 1), rs.getObject(i + 1));
			}
			ret.add(map);
		}
		if (outputParam != null) {// 获取输出参数，并存入参数outputList中
			for (int i = 0; i < outputParam.length; i++) {
				outputlst.add(stmt.getObject(i + 1));
			}
		}
		return ret;
	}

	/**
	 * 返回output参数类型
	 * 
	 * @param type
	 * @return
	 */
	private int getTypeByOutPutParam(String type) {
		if ("FLOAT".equals(type.toUpperCase())) {
			return Types.FLOAT;
		}
		if ("VARCHAR".equals(type.toUpperCase())) {
			return Types.VARCHAR;
		}
		if ("NVARCHAR".equals(type.toUpperCase())) {
			return Types.NVARCHAR;
		}
		if ("DOUBLE".equals(type.toUpperCase())) {
			return Types.DOUBLE;
		}
		if ("OTHER".equals(type.toUpperCase())) {
			return Types.OTHER;
		}
		if("INT".equals(type.toUpperCase())){
			return Types.INTEGER;
		}
		return Types.VARCHAR;
	}

	/**
	 * 执行通用的存储过程，查询返回list，更新返回0或1(0操作失败，1操作成功)
	 * 
	 * @throws SQLException
	 */
	public List executeProc(String procSql, Object[] parm) throws SQLException {
		final String proc = procSql;
		final Object[] param = parm;
		final List ret = new ArrayList();
		Map map;
		PreparedStatement pstmt = conn.prepareStatement(proc);
		if (param != null) {// 给各个参数赋值
			for (int i = 0; i < param.length; i++) {
				pstmt.setObject(i + 1, param[i]);
			}
		}
		ResultSet rs = pstmt.executeQuery();
		ResultSetMetaData rsmt = rs.getMetaData();
		int count = rsmt.getColumnCount();// 字段个数
		while (rs.next()) {
			map = new HashMap();
			for (int i = 0; i < count; i++) {
				map.put(rsmt.getColumnName(i + 1), rs.getObject(i + 1));
			}
			ret.add(map);
		}
		return ret;
	}
	
	public int returnOutParam(String procSql, Object[] parm) throws SQLException{
		final String proc = procSql;
		final Object[] param = parm;
		final List ret = new ArrayList();
		CallableStatement stmt = conn.prepareCall(proc);
		if (param != null) {// 给各个参数赋值
			for (int i = 0; i < param.length; i++) {
				stmt.setObject(i + 1, param[i]);
			}
		}
		stmt.registerOutParameter(parm.length, Types.INTEGER);
		ResultSet rs = stmt.executeQuery();
		return stmt.getInt(4);
	}

	/**
	 * 设置存放查询结果的Bean类，每个Bean类对象对应一条查询的结果记录
	 * 
	 * @param beanClass
	 *            ，如User.class
	 */
	public void setBeanClass(Class beanClass) {
		this.beanClass = beanClass;
	}

	/**
	 * 设置使用外部的Connection对象，如来自数据库连接池的连接
	 * 
	 * @param conn
	 *            Connection对象
	 */
	/**
	 * 资源释放
	 * 
	 * @throws SQLException
	 */
	public void close() {
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	@Override
	protected void finalize() throws Throwable {
		// TODO Auto-generated method stub
		close();
		super.finalize();
	}
	
	public static void main(String[] args) {
	}
}
